use anyhow::Result;
use chrono::{ DateTime, Utc };
use log::{ debug, trace };

use crate::model::tick::Tick;

use super::FUDB;

pub async fn insert_all(ticks: &Vec<Tick>) -> Result<u32> {
    let start = Utc::now();

    let query = FUDB.query(
        r#"
        FOR $v IN $ticks { 
            INSERT IGNORE INTO tick (id,security_id,time,price,volume,trade_count,s1b2j4) 
            VALUES ($v.id, $v.security_id, $v.time, $v.price, $v.volume, $v.trade_count, $v.s1b2j4)
        }
        "#
    ).bind(("ticks", ticks));

    // debug!(
    //     "(tick::upsert_all) len={}, {:?} \n",
    //     tick_vs.len(),
    //     tick_vs
    // );
    let _rs = query.await?;
    let end = Utc::now();
    debug!("(tick::insert_all) rs= {:?} spend time={:?}", _rs, end - start);

    Ok(1)
}

/// select a security's ticks
///
/// security_id: &str, e.g. SZ.KZZ.128136
///
/// pi: u16, page index, start from 0
///
/// pn: u16, page size,
///
pub async fn select(
    security_id: &str,
    after_klts: Option<DateTime<Utc>>,
    order_by: Option<&str>,
    pi: u16,
    pn: u16
) -> Result<Vec<Tick>> {
    let where_ = match &after_klts {
        Some(_) => "WHERE security_id=$security_id AND klts> $after_klts",
        None => "WHERE security_id=$security_id",
    };

    let start_time = Utc::now();

    let start = pi * pn;

    let order_by_ = if let Some(ob) = order_by { ob } else { "klts ASC" };
    let q_str = &format!(
        "SELECT * FROM tick {} ORDER BY {} LIMIT {} START {}",
        where_,
        order_by_,
        pn,
        start
    );

    // Fetch tick data within the specified date range using bind variables
    let mut query = FUDB.query(q_str);

    query = query.bind(("security_id", security_id));
    if let Some(t_) = &after_klts {
        query = query.bind(("after_klts", t_));
    }

    let mut response = query.await?;

    // println!("(tick::select2) result = {:?}", response);
    let ticks: Vec<Tick> = response.take(0)?;
    let end_time = Utc::now();
    // (tick::select) @2024-06-24T00:29:49.068139Z spent time = TimeDelta { secs: 2, nanos: 670045000 } for ticks.len=861
    trace!(
        "\n(tick::select) @{:?} spent time = {:?} for ticks.len={}\n",
        end_time,
        end_time - start_time,
        ticks.len()
    );
    Ok(ticks)
}

#[cfg(test)]
mod test {
    use crate::db;
    use crate::model::kline::test::tick_data;
    use crate::model::tick::Tick;
    use chrono::DateTime;
    use chrono::Local;
    use surrealdb::sql::Thing;

    #[tokio::test]
    async fn test_insert_ticks() {
        let _ = db::init().await.unwrap();
        let mut to_print_dot=true;
        let ticks: Vec<Tick> = tick_data
            .iter()
            .enumerate()
            .map(|(idx, &data)| {
                let parts: Vec<&str> = data.split(',').collect();
                let time_: DateTime<Local> = dateparser::parse(parts[0]).unwrap_or_default().into();
                if idx < 10 || idx > tick_data.len() - 10 {
                    println!("[{idx}] time: {:?}", time_);
                } else if to_print_dot{
                    println!("...................................");
                    to_print_dot = false;
                }
                Tick {
                    id: Some(
                        Thing::from(("tick", Tick::id_from("SH.KZZ.110052", &time_).as_str()))
                    ),
                    security_id: "SH.KZZ.110052".to_string(),
                    time: time_.into(),
                    price: parts[1].parse().unwrap(),
                    volume: parts[2].parse().unwrap(),
                    trade_count: parts[3].parse().unwrap(),
                    s1b2j4: parts[4].parse().unwrap(),
                    ..Default::default()
                }
            })
            .collect();
        let rs = db::tick::insert_all(&ticks).await;
        println!("{:?}", rs);
    }

    #[tokio::test]
    async fn test_select_ticks() {
        let _ = db::init().await.unwrap();
        let after_klts = dateparser::parse("2000-01-01 00:00:00").unwrap();
        let rs = super::select("SH.KZZ.110052", Some(after_klts), Some("klts ASC"), 0, 10000).await;
        if let Ok(ticks) = rs {
            let mut to_print_dot=true;
            for (idx, tick) in ticks.iter().enumerate() {
                if idx < 10 || idx > ticks.len() - 10 {
                    println!("[{idx}] time: {:?} price: {:?}", tick.time.with_timezone(&Local), tick.price);
                } else if to_print_dot{
                    println!("...................................");
                    to_print_dot = false;
                }
            }
        }
    }
}
